﻿Imports Microsoft.VisualBasic
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Public Class Quizsln
    Dim sqlConn As SqlConnection = New SqlConnection(getConnectionString())
   
    Public Function getConnectionString() As String
        Dim sqlConn As String = Nothing
        Try
            sqlConn = System.Configuration.ConfigurationManager.ConnectionStrings("serviceConnectionString").ToString
            Return sqlConn
        Catch ex As Exception
            Return sqlConn
        End Try
    End Function


    ''' <summary>
    ''' Gets every bannerID/email of every student from the master table 
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function getAllStudents() As DataSet
        Dim sConnection As String = New String(getConnectionString())
        Dim ds As DataSet = New DataSet()
        Using con As New SqlConnection(sConnection)
            con.Close()
            con.Open()
            Using com As New SqlCommand("select banner_id, email FROM COMSERV_BANNER_STUDENT", con)
                Dim Adapter As New SqlDataAdapter(com)
                Adapter.Fill(ds)
            End Using
            con.Close()
        End Using

        Return ds
    End Function


    ''' <summary>
    ''' gets the first name, last name combined from the master table
    ''' </summary>
    ''' <param name="bannerID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function getFnLn(ByVal bannerID As String) As String
        Dim sqlCommandText As String = "select (COALESCE(first_name,'')+ ' ' + COALESCE(last_name,''))as Name from COMSERV_BANNER_STUDENT where banner_id = '" & bannerID & "'"

        Dim sqlCommand As SqlCommand = New SqlCommand(sqlCommandText, sqlConn)

        sqlConn.Close()

        sqlConn.Open()
        Dim name As String = sqlCommand.ExecuteScalar()
        sqlConn.Close()
        Return name
    End Function

    ''' <summary>
    ''' gets the bannerId,Email, and attempts of every student that has taken the quiz before
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function getQuizStudents() As DataSet
        Dim sConnection As String = New String(getConnectionString())
        Dim ds As DataSet = New DataSet()
        Using con As New SqlConnection(sConnection)
            con.Close()
            con.Open()
            Using com As New SqlCommand("select banner_id, email, Attempts, pass FROM QUIZUSERS", con)
                Dim Adapter As New SqlDataAdapter(com)
                Adapter.Fill(ds)
            End Using
            con.Close()
        End Using

        Return ds
    End Function

 
    Public Sub insertQuizStudents(ByVal bannerId As String, ByVal email As String, ByVal passFail As String)
        Dim sConnection As String = New String(getConnectionString())
        Dim ds As DataSet = New DataSet()
        Using con As New SqlConnection(sConnection)
            con.Close()
            Using com As New SqlCommand("insert into QUIZUSERS (banner_id,email,attempts,pass)VALUES('" & bannerId & "','" & email & "',1,'" & passFail & "')", con)
                con.Open()
                com.ExecuteNonQuery()
                con.Close()
            End Using
        End Using

    End Sub

    Public Sub updateQuizAnswers(ByVal answerId As Integer, ByVal answer As String)
        Dim sConnection As String = New String(getConnectionString())
        Dim ds As DataSet = New DataSet()
        Using con As New SqlConnection(sConnection)
            con.Close()
            con.Open()
            Dim com As New SqlCommand("Update QUIZANSWERS SET ANSWER='" & answer & "' where ANSWER_ID ='" & answerId & "'", con)
            com.ExecuteNonQuery()
            con.Close()
        End Using
    End Sub

    Public Sub insertQuizQuestion(ByVal question As String)
        Dim sConnection As String = New String(getConnectionString())
        Dim ds As DataSet = New DataSet()
        Using con As New SqlConnection(sConnection)
            con.Close()
            con.Open()
            Dim com As New SqlCommand("insert into QUIZQUESTION (Question_ID,question) values ((SELECT ISNULL(MAX(question_ID) + 1, 1) FROM QUIZQUESTION),'" & question & "')", con)
            com.ExecuteNonQuery()
            con.Close()
        End Using
    End Sub

    Public Sub insertQuizAnswer(ByVal answer As String, ByVal questionId As String, ByVal correct As String)
        Dim sConnection As String = New String(getConnectionString())
        Dim ds As DataSet = New DataSet()
        Using con As New SqlConnection(sConnection)
            con.Close()
            con.Open()
            Dim com As New SqlCommand("insert into QUIZANSWERS (answer_id,question_Id, answer ,CORRECT_CHECK) values ((SELECT ISNULL(MAX(question_ID) + 1, 1) FROM QUIZANSWERS),'" & questionId & "', '" & answer & "', '" & correct & "')", con)
            com.ExecuteNonQuery()
            con.Close()
        End Using
    End Sub

    Public Sub updateQuizQuestions(ByVal questionId As Integer, ByVal question As String)
        Dim sConnection As String = New String(getConnectionString())
        Dim ds As DataSet = New DataSet()
        Using con As New SqlConnection(sConnection)
            con.Close()
            con.Open()
            Dim com As New SqlCommand("Update QUIZQUESTION SET Question='" & question & "' where QUESTION_ID ='" & questionId & "'", con)
            com.ExecuteNonQuery()
            con.Close()
        End Using
    End Sub
    Public Sub deleteQuizAnswers(ByVal answerId As Integer)
        Dim sConnection As String = New String(getConnectionString())
        Dim ds As DataSet = New DataSet()
        Using con As New SqlConnection(sConnection)
            con.Close()
            con.Open()
            Dim com As New SqlCommand("delete from QUIZANSWERS where ANSWER_ID ='" & answerId & "'", con)
            com.ExecuteNonQuery()
            con.Close()
        End Using
    End Sub
    Public Sub deleteQuizQuestions(ByVal questionId As Integer)
        Dim sConnection As String = New String(getConnectionString())
        Dim ds As DataSet = New DataSet()
        Using con As New SqlConnection(sConnection)
            con.Close()
            con.Open()
            Dim com As New SqlCommand("delete from QUIZANSWERS where QUESTION_ID ='" & questionId & "'", con)
            com.ExecuteNonQuery()
            Dim com2 As New SqlCommand("delete from QUIZQUESTION WHERE QUESTION_ID ='" & questionId & "'", con)
            com2.ExecuteNonQuery()
            con.Close()

        End Using
    End Sub
    Public Sub UpdateQuizStudents(ByVal bannerId As String, ByVal passFail As String)
        Dim sConnection As String = New String(getConnectionString())
        Dim ds As DataSet = New DataSet()
        Using con As New SqlConnection(sConnection)
            con.Close()

            Using com As New SqlCommand("UPDATE QUIZUSERS set attempts=2, pass='" & passFail & "' where banner_ID ='" & bannerId & "'", con)
                con.Open()
                com.ExecuteNonQuery()
                con.Close()
            End Using
        End Using

    End Sub

 
    Public Function getQuizAnswers(ByVal questionID As Integer) As DataTable
        Dim sConnection As String = New String(getConnectionString())
        Dim dt As DataTable = New DataTable()
        dt.Columns.Add("Answers")
        Using Con As New SqlConnection(sConnection)
            Con.Close()

            Con.Open()
            Using Com As New SqlCommand("SELECT ANSWER FROM QUIZANSWERS WHERE QUESTION_ID='" & questionID & "'", Con)
                Using RDR As SqlDataReader = Com.ExecuteReader()
                    If RDR.HasRows Then
                        Do While RDR.Read
                            Dim myString As String = RDR.GetString(0)
                            dt.Rows.Add(myString)
                        Loop
                    End If
                End Using
            End Using
            Con.Close()
        End Using
        Return dt
    End Function


    ''' <summary>
    ''' Used in the quizAdmin page to get the specific answers
    ''' </summary>
    ''' <param name="questionID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function getQuizAnswerAdmin(ByVal questionID As Integer) As DataTable
        Dim sConnection As String = New String(getConnectionString())
        Dim dt As DataTable = New DataTable()

        Dim strSql As String = "SELECT ANSWER, Answer_ID FROM QUIZANSWERS WHERE QUESTION_ID='" & questionID & "'"

        Using Con As New SqlConnection(sConnection)
            Con.Close()

            Con.Open()
            Using sqlDa As New SqlDataAdapter(strSql, Con)
                sqlDa.Fill(dt)
            End Using
            Con.Close()
        End Using
        Return dt
    End Function


    Public Sub deleteQuizStudentsAttempt(ByVal bannerId As String)
        Dim sConnection As String = New String(getConnectionString())
        Dim ds As DataSet = New DataSet()
        Using con As New SqlConnection(sConnection)
            con.Close()

            Using com As New SqlCommand("delete from QUIZUSERS where banner_ID ='" & bannerId & "'", con)
                con.Open()
                com.ExecuteNonQuery()
                con.Close()
            End Using
        End Using

    End Sub
    Public Function getQuizUsersCount() As Integer
        Dim sqlCommandText As String = "SELECT Count(Banner_id) FROM QUIZUSERS"

        Dim sqlCommand As SqlCommand = New SqlCommand(sqlCommandText, sqlConn)

        sqlConn.Close()

        sqlConn.Open()
        Dim students As Integer = sqlCommand.ExecuteScalar()
        sqlConn.Close()
        Return students
    End Function


    Public Function getQuizUsersForReset() As DataTable
        Dim sConnection As String = New String(getConnectionString())
        Dim dt As DataTable = New DataTable()

        Dim strSql As String = "select quizusers.banner_ID, cbs.first_name, cbs.last_name from QUIZUSERS inner join COMSERV_BANNER_STUDENT cbs on cbs.banner_id = quizUsers.banner_ID"

        Using Con As New SqlConnection(sConnection)
            Con.Close()

            Con.Open()
            Using sqlDa As New SqlDataAdapter(strSql, Con)
                sqlDa.Fill(dt)
            End Using
            Con.Close()
        End Using
        Return dt
    End Function
   

    Public Function getQuizQuestions(ByVal questionID As Integer) As String

        Dim sqlCommandText As String = "SELECT QUESTION FROM QUIZQUESTION WHERE QUESTION_ID='" & questionID & "'"

        Dim sqlCommand As SqlCommand = New SqlCommand(sqlCommandText, sqlConn)

        sqlConn.Close()

        sqlConn.Open()
        Dim quizQuestion As String = sqlCommand.ExecuteScalar()
        sqlConn.Close()
        Return quizQuestion
    End Function
    Public Function getCorrectAnswer(ByVal questionID As Integer) As DataTable
        Dim sConnection As String = New String(getConnectionString())
        Dim dt As DataTable = New DataTable()

        Dim strSql As String = "SELECT ANSWER FROM QUIZANSWERS WHERE QUESTION_ID='" & questionID & "'AND CORRECT_CHECK='Y'"

        Using Con As New SqlConnection(sConnection)
            Con.Close()

            Con.Open()
            Using sqlDa As New SqlDataAdapter(strSql, Con)
                sqlDa.Fill(dt)
            End Using
            Con.Close()
        End Using
        Return dt
    End Function

    Public Function getQuizId() As DataTable
        Dim sConnection As String = New String(getConnectionString())
        Dim dt As DataTable = New DataTable()
        dt.Columns.Add("Id")
        Using Con As New SqlConnection(sConnection)
            Con.Close()

            Con.Open()
            Using Com As New SqlCommand("SELECT Question_Id FROM QUIZQUESTION", Con)
                Using RDR As SqlDataReader = Com.ExecuteReader()
                    If RDR.HasRows Then
                        Do While RDR.Read
                            Dim myString As Integer = RDR.GetInt32(0)
                            dt.Rows.Add(myString)
                        Loop
                    End If
                End Using
            End Using
            Con.Close()
        End Using
        Return dt
    End Function


    Public Function getAnswerCount(ByVal questionId As Integer) As Integer
        Dim sqlCommandText As String = "SELECT Count(ANSWER) FROM QUIZANSWERS WHERE QUESTION_ID ='" & questionId & "'"

        Dim sqlCommand As SqlCommand = New SqlCommand(sqlCommandText, sqlConn)

        sqlConn.Close()

        sqlConn.Open()
        Dim quizQuestion As Integer = sqlCommand.ExecuteScalar()
        sqlConn.Close()
        Return quizQuestion
    End Function
    Public Function getQuizQuestionsCount() As Integer

        Dim sqlCommandText As String = "SELECT Count(QUESTION) FROM QUIZQUESTION"

        Dim sqlCommand As SqlCommand = New SqlCommand(sqlCommandText, sqlConn)

        sqlConn.Close()

        sqlConn.Open()
        Dim quizQuestion As Integer = sqlCommand.ExecuteScalar()
        sqlConn.Close()
        Return quizQuestion
    End Function

End Class
